package com.lyouu.dataminer.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.util.Constants;

public class MdSubjectDao {
	public int insert(int sid, int catid, String name, String owner,
			String imgUrl) throws SQLException {
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		String sql = "INSERT INTO md_subject"
				+ "(sid,city_id,aid,pid,catid,name,owner,pictures,thumb,cuid,creator,addtime,finer,level) VALUES"
				+ "(?,'1','2','7',?,?,?,'1',?,'0',?,?,'8','4')";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, sid);
		if (name.length() > 54)
			name = name.substring(0, 54);
		stmt.setInt(2, catid);
		stmt.setString(3, name);
		stmt.setString(4, owner);
		stmt.setString(5, imgUrl);
		stmt.setString(6, Constants.MD_SUBJECT_CREATOR);
		stmt.setInt(7, (int) (System.currentTimeMillis() / 1000));
		return stmt.executeUpdate();
	}

	public String getTourNameBySid(int sid) throws SQLException {
		String name = "";
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		String sql = "SELECT name FROM md_subject_bustour WHERE sid=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, sid);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			name = rs.getString(1);
		}
		rs.close();
		conn.close();
		return name;
	}

	public int getMaxSid() throws SQLException {
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		int maxSid = 0;
		String sql = "SELECT sid FROM md_subject";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			if (maxSid < rs.getInt(1))
				maxSid = rs.getInt(1);
		}
		conn.close();
		return maxSid;
	}

	public int getMinSidByOwner(String owner, int catid) throws SQLException {
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		int minSid = Integer.MAX_VALUE;
		String sql = "SELECT sid FROM md_subject WHERE owner=? AND catid=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, owner);
		stmt.setInt(2, catid);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			if (minSid > rs.getInt(1))
				minSid = rs.getInt(1);
		}
		conn.close();
		return minSid;
	}

	public int getMaxSidByOwner(String owner, int catid) throws SQLException {
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		int maxSid = 0;
		String sql = "SELECT sid FROM md_subject WHERE owner=? AND catid=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, owner);
		stmt.setInt(2, catid);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			if (maxSid < rs.getInt(1))
				maxSid = rs.getInt(1);
		}
		conn.close();
		return maxSid;
	}
}
